package com.aat.dxfy.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestDemo {
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://127.0.0.1:3306/disconf";
			String user = "root";
			String pass = "root";
			conn = DriverManager.getConnection(url, user, pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void main(String[] args) {
		try {
			Connection conn = getConnection();
			//
			List<String> tabs = getTabs(conn);
			for (String tab : tabs) {
				List<TabFiled> fileds = getFileds(conn, tab);
				getDatas(conn, tab, fileds);
			}
			//
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static List<String> getTabs(Connection conn) {
		try {
			String sql = "show tables";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			List<String> tabs = new ArrayList<>();
			while (rs.next()) {
				tabs.add(rs.getString(1));
			}
			return tabs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static List<TabFiled> getFileds(Connection conn, String tab) {
		try {
			// 得到表的字段结构
			String querySql = "SELECT * FROM " + tab + " LIMIT 1";
			PreparedStatement ps = conn.prepareStatement(querySql);
			ResultSet res = ps.executeQuery();
			ResultSetMetaData data = res.getMetaData();

			List<TabFiled> list = new ArrayList<>();

			for (int i = 1; i <= data.getColumnCount(); i++) {
				String columnName = data.getColumnName(i);
				String columnClassName = data.getColumnClassName(i);
				TabFiled filed = new TabFiled();
				filed.setColumnName(columnName);
				filed.setColumnClassName(columnClassName);
				list.add(filed);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static List<DataSql> getDatas(Connection conn, String tab, List<TabFiled> filedNameAndClassList) {
		try {
			List<DataSql> list=new ArrayList<>();
			
			String dataQuerySql = "SELECT ";
			String dataInsertSql = "INSERT INTO " + tab + "(";
			String dataUpdateSql = "UPDATE " + tab + " SET ";

			for (TabFiled filed : filedNameAndClassList) {
				dataQuerySql += filed.getColumnName() + ",";
				dataInsertSql += filed.getColumnName() + ",";
			}
			dataQuerySql = dataQuerySql.substring(0, dataQuerySql.length() - 1);
			dataInsertSql = dataInsertSql.substring(0, dataInsertSql.length() - 1);
			dataQuerySql += " FROM " + tab;
			dataInsertSql += ") VALUES (";

			// 查询数据
			PreparedStatement pst = conn.prepareStatement(dataQuerySql);
			ResultSet rest = pst.executeQuery();

			// 取数据
			while (rest.next()) {
				String dataInsertSqlDo = dataInsertSql;
				String dataUpdateSqlDo = dataUpdateSql;
				int index = 1;

				// 一行数据取出
				for (TabFiled filed : filedNameAndClassList) {
					String ClassName = filed.getColumnClassName();
					String ColumnName = filed.getColumnName();
					dataUpdateSqlDo += ColumnName + "=";
					if (ClassName.contains("String")) {
						dataInsertSqlDo += "'" + rest.getString(index) + "',";
						dataUpdateSqlDo += "'" + rest.getString(index) + "',";
					}
					if (ClassName.contains("Long")) {
						dataInsertSqlDo += rest.getLong(index) + ",";
						dataUpdateSqlDo += rest.getLong(index) + ",";
					}
					if (ClassName.contains("Integer")) {
						dataInsertSqlDo += rest.getInt(index) + ",";
						dataUpdateSqlDo += rest.getLong(index) + ",";
					}
					if (ClassName.contains("Date")) {
						dataInsertSqlDo += "'" + rest.getDate(index) + "',";
						dataUpdateSqlDo += rest.getLong(index) + ",";
					}
					index++;
				}

				dataInsertSqlDo = dataInsertSqlDo.substring(0, dataInsertSqlDo.length() - 1);
				dataUpdateSqlDo = dataUpdateSqlDo.substring(0, dataUpdateSqlDo.length() - 1);
				dataInsertSqlDo += ")";

				TabFiled filed0 = filedNameAndClassList.get(0);
				if (filed0.getColumnClassName().contains("String")) {
					dataUpdateSqlDo += " WHERE " + filed0.getColumnName() + "=" + rest.getString(1);
				} else if (filed0.getColumnClassName().contains("Long")) {
					dataUpdateSqlDo += " WHERE " + filed0.getColumnName() + "=" + rest.getLong(1);
				} else {
					dataUpdateSqlDo += " WHERE " + filed0.getColumnName() + "=" + rest.getInt(1);
				}

				DataSql sqlObj=new DataSql();
				sqlObj.setInsertSql(dataInsertSqlDo);
				sqlObj.setUpdateSql(dataUpdateSqlDo);
				
				list.add(sqlObj);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	static class TabFiled {
		private String columnName;
		private String columnClassName;

		public String getColumnName() {
			return columnName;
		}

		public void setColumnName(String columnName) {
			this.columnName = columnName;
		}

		public String getColumnClassName() {
			return columnClassName;
		}

		public void setColumnClassName(String columnClassName) {
			this.columnClassName = columnClassName;
		}

	}

	static class DataSql {
		private String insertSql;
		private String updateSql;

		public String getInsertSql() {
			return insertSql;
		}

		public void setInsertSql(String insertSql) {
			this.insertSql = insertSql;
		}

		public String getUpdateSql() {
			return updateSql;
		}

		public void setUpdateSql(String updateSql) {
			this.updateSql = updateSql;
		}
	}

	public static void test() {
		Connection conn = getConnection();
		String sql = "select * from area";
		PreparedStatement stmt;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			for (int i = 1; i <= data.getColumnCount(); i++) {
				// 获得所有列的数目及实际列数
				int columnCount = data.getColumnCount();
				// 获得指定列的列名
				String columnName = data.getColumnName(i);
				// 获得指定列的列值
				int columnType = data.getColumnType(i);
				// 获得指定列的数据类型名
				String columnTypeName = data.getColumnTypeName(i);
				// 所在的Catalog名字
				String catalogName = data.getCatalogName(i);
				// 对应数据类型的类
				String columnClassName = data.getColumnClassName(i);
				// 在数据库中类型的最大字符个数
				int columnDisplaySize = data.getColumnDisplaySize(i);
				// 默认的列的标题
				String columnLabel = data.getColumnLabel(i);
				// 获得列的模式
				String schemaName = data.getSchemaName(i);
				// 某列类型的精确度(类型的长度)
				int precision = data.getPrecision(i);
				// 小数点后的位数
				int scale = data.getScale(i);
				// 获取某列对应的表名
				String tableName = data.getTableName(i);
				// 是否自动递增
				boolean isAutoInctement = data.isAutoIncrement(i);
				// 在数据库中是否为货币型
				boolean isCurrency = data.isCurrency(i);
				// 是否为空
				int isNullable = data.isNullable(i);
				// 是否为只读
				boolean isReadOnly = data.isReadOnly(i);
				// 能否出现在where中
				boolean isSearchable = data.isSearchable(i);
				System.out.println(columnCount);
				System.out.println("获得列" + i + "的字段名称:" + columnName);
				System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
				System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
				System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
				System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
				System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
				System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
				System.out.println("获得列" + i + "的模式:" + schemaName);
				System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
				System.out.println("获得列" + i + "小数点后的位数:" + scale);
				System.out.println("获得列" + i + "对应的表名:" + tableName);
				System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
				System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
				System.out.println("获得列" + i + "是否为空:" + isNullable);
				System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
				System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}